package goodenglish.infrastructure.repository.db;

import goodenglish.domain.model.LazyList;
import goodenglish.domain.model.lexicon.LexiconLazy;
import goodenglish.domain.model.lexicon.LexiconRepository;
import goodenglish.domain.model.subt.Subt;
import goodenglish.domain.model.subt.SubtRepository;
import goodenglish.domain.model.subt.SubtSentence;
import goodenglish.interfaces.BeanFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class SubtRepositoryDb implements SubtRepository {
	@Override
	public List<Subt> findSubts() {
		List<Subt> subts = new ArrayList<Subt>();
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DBUtil.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select id, name,lexicon_id from subt;");
			while (rs.next()) {
				Subt subt = new Subt();

				subt.setId(rs.getString("id"));
				subt.setName(rs.getString("name"));
				subt.setLexicon(new LexiconLazy(rs.getString("lexicon_id")));
				subt.setSubtSetences(new LazyList<SubtSentence>());
				subts.add(subt);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeStatement(stmt);
			DBUtil.closeConnection(conn);
		}
		return subts;
	}

	@Override
	public Subt getSubtByName(String name) {
		Subt subt = null;
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs1;
		try {
			conn = DBUtil.getConnection();
			prep = conn.prepareStatement("select id, name ,lexicon_id from subt where name=?;");
			prep.setString(1, name);
			rs1 = prep.executeQuery();
			if (rs1.next()) {
				subt = new Subt();
				subt.setId(rs1.getString("id"));
				subt.setName(rs1.getString("name"));
				subt.setLexicon(new LexiconLazy(rs1.getString("lexicon_id")));
				subt.setSubtSetences(new LazyList<SubtSentence>());
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeStatement(prep);
			DBUtil.closeConnection(conn);
		}

		return subt;
	}

	@Override
	public void loadSubtSentence(final Subt subt) {
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs1;
		try {
			conn = DBUtil.getConnection();

			// 加载句子
			prep = conn
					.prepareStatement("select chStr, enStr,stimeStr,etimeStr,stime,etime from SubtSentence where subt_id=?;");
			prep.setString(1, subt.getId());
			rs1 = prep.executeQuery();
			List<SubtSentence> subtSetences = new ArrayList<SubtSentence>();
			while (rs1.next()) {
				SubtSentence sentence = new SubtSentence();
				sentence.setChStr(rs1.getString("chStr"));
				sentence.setEnStr(rs1.getString("enStr"));
				sentence.setStimeStr(rs1.getString("stimeStr"));
				sentence.setEtimeStr(rs1.getString("etimeStr"));
				sentence.setStime(rs1.getInt("stime"));
				sentence.setEtime(rs1.getInt("etime"));
				subtSetences.add(sentence);
			}
			subt.setSubtSetences(subtSetences);

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeStatement(prep);
			DBUtil.closeConnection(conn);
		}
	}

	@Override
	public void saveSubt(final Subt subt) {
		Connection conn = null;
		PreparedStatement prep = null;
		Statement stmt = null;
		try {
			conn = DBUtil.getConnection();
			stmt = conn.createStatement();
			prep = conn.prepareStatement("insert into subt(id,name,lexicon_id) values (?,?,?);");
			prep.setString(1, subt.getId());
			prep.setString(2, subt.getName());
			prep.setString(3, subt.getLexicon().getId());
			prep.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeStatement(stmt, prep);
			DBUtil.closeConnection(conn);
		}
		saveSubtSentences(subt);

	}

	private void saveSubtSentences(final Subt subt) {

		Connection conn = null;
		PreparedStatement prep = null;
		Statement stmt = null;
		try {
			conn = DBUtil.getConnection();
			DBUtil.beginBatch(conn);
			stmt = conn.createStatement();
			prep = conn
					.prepareStatement("insert into SubtSentence(chStr, enStr,stimeStr,etimeStr,stime,etime,subt_id) values (?,?,?,?,?,?,?);");
			for (SubtSentence sentence : subt.getSubtSentences()) {
				prep.setString(1, sentence.getChStr());
				prep.setString(2, sentence.getEnStr());
				prep.setString(3, sentence.getStimeStr());
				prep.setString(4, sentence.getEtimeStr());
				prep.setLong(5, sentence.getStime());
				prep.setLong(6, sentence.getEtime());
				prep.setString(7, subt.getId());
				prep.addBatch();
			}

			prep.executeBatch();
			DBUtil.commitBatch(conn);
		} catch (Exception e) {
			throw new DaoException("保存句子出错", e);
		} finally {
			DBUtil.closeStatement(stmt, prep);
			DBUtil.closeConnection(conn);
		}

	}

	private LexiconRepository lexiconRepository = BeanFactory.getSingleInstance(LexiconRepositoryDb.class);

}
